﻿/*
Deployment script for MetaCollection.LocalDatabase
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "MetaCollection.LocalDatabase"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"

GO
USE [master]

GO
:on error exit
GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END

GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [PrimaryFileName], FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf')
    LOG ON (NAME = [PrimaryLogFileName], FILENAME = '$(DefaultDataPath)$(DatabaseName)_log.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXECUTE sp_dbcmptlevel [$(DatabaseName)], 100;


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY FULL,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF 
            WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CLOSE OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_UPDATE_STATISTICS_ASYNC ON,
                PAGE_VERIFY NONE,
                DATE_CORRELATION_OPTIMIZATION OFF,
                DISABLE_BROKER,
                PARAMETERIZATION SIMPLE,
                SUPPLEMENTAL_LOGGING OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET TRUSTWORTHY OFF,
        DB_CHAINING OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET HONOR_BROKER_PRIORITY OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
USE [$(DatabaseName)]

GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';


GO
/*
 Pre-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be executed before the build script	
 Use SQLCMD syntax to include a file into the pre-deployment script			
 Example:      :r .\filename.sql								
 Use SQLCMD syntax to reference a variable in the pre-deployment script		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/




GO
PRINT N'Creating [StructureService]...';


GO
CREATE USER [StructureService] WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = [structure];


GO
PRINT N'Creating [structure]...';


GO
CREATE SCHEMA [structure]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [structure].[Item]...';


GO
CREATE TABLE [structure].[Item] (
    [Id]               UNIQUEIDENTIFIER NOT NULL,
    [ParentId]         UNIQUEIDENTIFIER NULL,
    [ExternalId]       VARCHAR (100)    NULL,
    [PublishedVersion] INT              NULL,
    [ItemClaimId]      UNIQUEIDENTIFIER NOT NULL,
    [Href]             VARCHAR (2000)   NOT NULL
) ON [PRIMARY];


GO
PRINT N'Creating PK_Item...';


GO
ALTER TABLE [structure].[Item]
    ADD CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];


GO
PRINT N'Creating [structure].[Item].[NonClusteredIndex]...';


GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex]
    ON [structure].[Item]([Id] ASC, [ParentId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
    ON [PRIMARY];


GO
PRINT N'Creating [structure].[ItemClaim]...';


GO
CREATE TABLE [structure].[ItemClaim] (
    [Id]          UNIQUEIDENTIFIER NOT NULL,
    [ClaimType]   VARCHAR (255)    NOT NULL,
    [ClaimValue]  VARCHAR (50)     NOT NULL,
    [AccessFlags] INT              NOT NULL
) ON [PRIMARY];


GO
PRINT N'Creating PK_ItemClaim...';


GO
ALTER TABLE [structure].[ItemClaim]
    ADD CONSTRAINT [PK_ItemClaim] PRIMARY KEY CLUSTERED ([Id] ASC, [ClaimType] ASC, [ClaimValue] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [structure].[ItemEvent]...';


GO
CREATE TABLE [structure].[ItemEvent] (
    [Id]        INT              IDENTITY (1, 1) NOT NULL,
    [ItemId]    UNIQUEIDENTIFIER NOT NULL,
    [Version]   INT              NULL,
    [EventDate] DATETIME         NOT NULL,
    [EventType] VARCHAR (50)     NOT NULL,
    [Message]   VARCHAR (512)    NULL
) ON [PRIMARY];


GO
PRINT N'Creating PK_EventLog...';


GO
ALTER TABLE [structure].[ItemEvent]
    ADD CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [structure].[ItemRelationship]...';


GO
CREATE TABLE [structure].[ItemRelationship] (
    [ItemId]           UNIQUEIDENTIFIER NOT NULL,
    [Version]          INT              NOT NULL,
    [RelationshipType] VARCHAR (50)     NOT NULL,
    [TargetItem]       VARCHAR (512)    NOT NULL,
    [State]            VARCHAR (1024)   NULL
);


GO
PRINT N'Creating PK_ItemRelationship...';


GO
ALTER TABLE [structure].[ItemRelationship]
    ADD CONSTRAINT [PK_ItemRelationship] PRIMARY KEY CLUSTERED ([ItemId] ASC, [Version] ASC, [RelationshipType] ASC, [TargetItem] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [structure].[ItemResource]...';


GO
CREATE TABLE [structure].[ItemResource] (
    [ItemId]        UNIQUEIDENTIFIER NOT NULL,
    [Version]       INT              NOT NULL,
    [FileExtension] VARCHAR (10)     NULL,
    [Resource]      VARBINARY (MAX)  NULL,
    [RowIdentifier] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
) ON [PRIMARY];


GO
PRINT N'Creating PK_ItemResource...';


GO
ALTER TABLE [structure].[ItemResource]
    ADD CONSTRAINT [PK_ItemResource] PRIMARY KEY CLUSTERED ([ItemId] ASC, [Version] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];


GO
PRINT N'Creating [structure].[ItemResource].[ItemResourceRowIndex]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [ItemResourceRowIndex]
    ON [structure].[ItemResource]([RowIdentifier] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
    ON [PRIMARY];


GO
PRINT N'Creating [structure].[ItemVersion]...';


GO
CREATE TABLE [structure].[ItemVersion] (
    [ItemId]          UNIQUEIDENTIFIER NOT NULL,
    [Version]         INT              NOT NULL,
    [LeftVersion]     INT              NULL,
    [RightVersion]    INT              NULL,
    [Comment]         VARCHAR (255)    NULL,
    [Name]            VARCHAR (255)    NOT NULL,
    [MimeType]        VARCHAR (50)     NOT NULL,
    [SpecificationId] UNIQUEIDENTIFIER NOT NULL,
    [CreatedBy]       VARCHAR (255)    NOT NULL,
    [LastModified]    DATETIME         NOT NULL,
    [PropertyData]    VARBINARY (1)    NULL,
    [RowIdentifier]   UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
) ON [PRIMARY];


GO
PRINT N'Creating PK_ItemVersion...';


GO
ALTER TABLE [structure].[ItemVersion]
    ADD CONSTRAINT [PK_ItemVersion] PRIMARY KEY CLUSTERED ([ItemId] ASC, [Version] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];


GO
PRINT N'Creating [structure].[ItemVersion].[ItemVersionRowIndex]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [ItemVersionRowIndex]
    ON [structure].[ItemVersion]([RowIdentifier] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
    ON [PRIMARY];


GO
PRINT N'Creating [structure].[ItemVersion].[NonClusteredIndex]...';


GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex]
    ON [structure].[ItemVersion]([ItemId] ASC, [Name] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
    ON [PRIMARY];


GO
PRINT N'Creating On column: Id...';


GO
ALTER TABLE [structure].[Item]
    ADD DEFAULT (newid()) FOR [Id];


GO
PRINT N'Creating On column: AccessFlags...';


GO
ALTER TABLE [structure].[ItemClaim]
    ADD DEFAULT (0) FOR [AccessFlags];


GO
PRINT N'Creating On column: EventDate...';


GO
ALTER TABLE [structure].[ItemEvent]
    ADD DEFAULT (getdate()) FOR [EventDate];


GO
PRINT N'Creating DF_ItemResource_RowId...';


GO
ALTER TABLE [structure].[ItemResource]
    ADD CONSTRAINT [DF_ItemResource_RowId] DEFAULT (newid()) FOR [RowIdentifier];


GO
PRINT N'Creating [structure].[Item].[IdParentId]...';


GO
CREATE STATISTICS [IdParentId]
    ON [structure].[Item]([ParentId], [Id]);


GO
PRINT N'Creating [structure].[ItemVersion].[NameItemId]...';


GO
CREATE STATISTICS [NameItemId]
    ON [structure].[ItemVersion]([Name], [ItemId]);


GO
PRINT N'Creating FK_Item_Item...';


GO
ALTER TABLE [structure].[Item] WITH NOCHECK
    ADD CONSTRAINT [FK_Item_Item] FOREIGN KEY ([ParentId]) REFERENCES [structure].[Item] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Event_Item...';


GO
ALTER TABLE [structure].[ItemEvent] WITH NOCHECK
    ADD CONSTRAINT [FK_Event_Item] FOREIGN KEY ([ItemId]) REFERENCES [structure].[Item] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ItemRelationship_ItemVersion...';


GO
ALTER TABLE [structure].[ItemRelationship] WITH NOCHECK
    ADD CONSTRAINT [FK_ItemRelationship_ItemVersion] FOREIGN KEY ([ItemId], [Version]) REFERENCES [structure].[ItemVersion] ([ItemId], [Version]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ItemResource_ItemVersion...';


GO
ALTER TABLE [structure].[ItemResource] WITH NOCHECK
    ADD CONSTRAINT [FK_ItemResource_ItemVersion] FOREIGN KEY ([ItemId], [Version]) REFERENCES [structure].[ItemVersion] ([ItemId], [Version]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ItemVersion_Item...';


GO
ALTER TABLE [structure].[ItemVersion] WITH NOCHECK
    ADD CONSTRAINT [FK_ItemVersion_Item] FOREIGN KEY ([ItemId]) REFERENCES [structure].[Item] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating [structure].[GetItemPath]...';


GO
CREATE FUNCTION [structure].[GetItemPath]
(
	@itemId uniqueidentifier, 
	@version int
)
RETURNS varchar(500)
AS
BEGIN
DECLARE @Path varchar(500)

;WITH PathParts (ParentId, ItemId, ObjectPath)
AS
(
    SELECT 
		structure.Item.ParentId,
		structure.Item.Id,
		CAST(structure.ItemVersion.Name as varchar(255))
    FROM
		structure.Item
		INNER JOIN
		structure.ItemVersion
		ON
		(structure.Item.Id = structure.ItemVersion.ItemId)
    WHERE
		structure.Item.Id = @itemId
	AND
		(structure.ItemVersion.Version = @version)
    UNION ALL
    SELECT
		structure.Item.ParentId,
		structure.Item.Id,
		CAST (structure.ItemVersion.Name + '/' + PathParts.ObjectPath AS varchar(255))
    FROM
		structure.Item
		INNER JOIN
		structure.ItemVersion
		ON
		(structure.Item.Id = structure.ItemVersion.ItemId)
			INNER JOIN
			PathParts
			ON
			structure.Item.Id = PathParts.ParentId
    WHERE
		(structure.ItemVersion.Version =  structure.Item.PublishedVersion)
)
 

SELECT @Path =  'col://' + (objectpath ) FROM PathParts WHERE ParentId IS NULL
RETURN @Path
END
GO
PRINT N'Creating [structure].[GetLatestVersion]...';


GO
CREATE FUNCTION [structure].[GetLatestVersion]
(
	@itemId uniqueIdentifier
)
RETURNS INT
AS
BEGIN
	 RETURN (SELECT max(structure.ItemVersion.Version) FROM structure.ItemVersion WHERE (structure.ItemVersion.ItemId = @itemId))
END
GO
PRINT N'Creating [structure].[FK_Item_Item].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Parent child heirarchy', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'Item', @level2type = N'CONSTRAINT', @level2name = N'FK_Item_Item';


GO
PRINT N'Creating [structure].[ItemClaim].[Id].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Item Claim Set Record Identifier', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemClaim', @level2type = N'COLUMN', @level2name = N'Id';


GO
PRINT N'Creating [structure].[ItemClaim].[ClaimType].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Unique ClaimType to demand  in order to grant the AccessFlags to the ClaimPrincipal for the Item', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemClaim', @level2type = N'COLUMN', @level2name = N'ClaimType';


GO
PRINT N'Creating [structure].[ItemClaim].[ClaimValue].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Value of the ClaimType property that must be present for ClaimType for this record to apply to a ClaimPrincipal', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemClaim', @level2type = N'COLUMN', @level2name = N'ClaimValue';


GO
PRINT N'Creating [structure].[ItemClaim].[AccessFlags].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Binary flags indicating the permission rights associated with this record', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemClaim', @level2type = N'COLUMN', @level2name = N'AccessFlags';


GO
PRINT N'Creating [structure].[ItemResource].[Version].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Version Number', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemResource', @level2type = N'COLUMN', @level2name = N'Version';


GO
PRINT N'Creating [structure].[ItemVersion].[Version].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Version Number', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemVersion', @level2type = N'COLUMN', @level2name = N'Version';


GO
PRINT N'Creating [structure].[ItemVersion].[LeftVersion].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Parent Version this Version drives from (0 = no parent)', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemVersion', @level2type = N'COLUMN', @level2name = N'LeftVersion';


GO
PRINT N'Creating [structure].[ItemVersion].[RightVersion].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Optional second Version merged with LeftVersion when creating this Version', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemVersion', @level2type = N'COLUMN', @level2name = N'RightVersion';


GO
PRINT N'Creating [structure].[ItemVersion].[Comment].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Optional comment added when this version was created', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemVersion', @level2type = N'COLUMN', @level2name = N'Comment';


GO
PRINT N'Creating [structure].[ItemVersion].[Name].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Name used to identify this item in the heirarchy.  Used as a path segment in the URI', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemVersion', @level2type = N'COLUMN', @level2name = N'Name';


GO
PRINT N'Creating [structure].[ItemVersion].[MimeType].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Multipurpose Internet Mail Extensions (content type of the item)', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemVersion', @level2type = N'COLUMN', @level2name = N'MimeType';


GO
PRINT N'Creating [structure].[ItemVersion].[SpecificationId].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Unique Id that is associated with an Item Specification which defines the extended model properties stored in ItemProperties.', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemVersion', @level2type = N'COLUMN', @level2name = N'SpecificationId';


GO
PRINT N'Creating [structure].[ItemVersion].[CreatedBy].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Principal who created this version of the  Item', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemVersion', @level2type = N'COLUMN', @level2name = N'CreatedBy';


GO
PRINT N'Creating [structure].[ItemVersion].[LastModified].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Last Modified Time for this  Version.  Note that the last modified time of the resource may be different than the heirarchy data', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemVersion', @level2type = N'COLUMN', @level2name = N'LastModified';


GO
PRINT N'Creating [structure].[ItemVersion].[PropertyData].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Binary Serialized Property Data.  This field stores the extensible properties for each item', @level0type = N'SCHEMA', @level0name = N'structure', @level1type = N'TABLE', @level1name = N'ItemVersion', @level2type = N'COLUMN', @level2name = N'PropertyData';


GO
-- Refactoring step to update target server with deployed transaction logs
CREATE TABLE  [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
GO
sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
GO
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('45fef6c2-07f5-4c24-a3e6-adb7c6dd4e83')

GO

GO
/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script		
 Use SQLCMD syntax to include a file into the post-deployment script			
 Example:      :r .\filename.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/





GO
PRINT N'Checking existing data against newly created constraints';


GO
USE [$(DatabaseName)];


GO
ALTER TABLE [structure].[Item] WITH CHECK CHECK CONSTRAINT [FK_Item_Item];

ALTER TABLE [structure].[ItemEvent] WITH CHECK CHECK CONSTRAINT [FK_Event_Item];

ALTER TABLE [structure].[ItemRelationship] WITH CHECK CHECK CONSTRAINT [FK_ItemRelationship_ItemVersion];

ALTER TABLE [structure].[ItemResource] WITH CHECK CHECK CONSTRAINT [FK_ItemResource_ItemVersion];

ALTER TABLE [structure].[ItemVersion] WITH CHECK CHECK CONSTRAINT [FK_ItemVersion_Item];


GO
